Attribute VB_Name = "Database"
Option Explicit

Private Type TField
 FieldName As String
 FieldValue As String
End Type

Private Type TColumnPriv
 Host As String
 Db As String
 User As String
 Table_name As String
 Column_Name As String
 Column_Priv As String
End Type

Private Type TProcsPriv
 Host As String
 Db As String
 User As String
 Routine_name As String
 Routine_type As String
 Grantor As String
 Proc_priv As String
End Type

Private Type TTablesPriv
 Host As String
 Db As String
 User As String
 Table_name As String
 Grantor As String
 Table_Priv As String
 Column_Priv As String
End Type

Private Type Tdb
 Host As String
 Db As String
 User As String
 Select_priv As String
 Insert_priv As String
 Update_priv As String
 Delete_priv As String
 Create_priv As String
 Drop_priv As String
 Grant_priv As String
 References_priv As String
 Index_priv As String
 Alter_priv As String
 Create_tmp_table_priv As String
 Lock_tables_priv As String
 Create_view_priv As String
 Show_view_priv As String
 Create_routine_priv As String
 Alter_routine_priv As String
 Execute_priv As String
End Type

Private Fields() As TField
Private ColumnPriv As TColumnPriv
Private ProcPriv As TProcsPriv
Private TablePriv As TTablesPriv
Private Db As Tdb

Public Mct As New ADODB.Connection
Public Rs As ADODB.Recordset
Public Sql As String

Public nGroups As Integer
Public nUsers As Integer
Public nNonUsers As Integer
Public Groups() As String
Public Users() As String
Public NonUsers() As String

'#########################################################################

Public Sub Synchronize()
 Dim i As Integer
 Dim j As Integer
 Dim k As Integer
 Dim L As Integer
 Dim m As Integer
 Dim Count As Integer
 Dim nFields As Integer
 Dim UserName As String
 Lib.AdLog "Beginning Synchronization"
 FrmMain.LblProgress.Caption = "Synchronization instanciated..."
 FrmMain.CmdApply.Enabled = False
 '1) Users (global priv)
 FrmMain.LblProgress.Caption = "Updating global privs..."
 Sql = "SELECT * FROM user"
 Set Rs = Mct.Execute(Sql)
 nFields = Rs.Fields.Count
 For i = 0 To nFields - 1
  With Rs.Fields(i)
   If Right(.Name, 5) = "_priv" Then Count = Count + 1
  End With
 Next i
 ReDim Fields(Count)
 Count = 0
 For i = 0 To nFields - 1
  With Fields(Count)
   If Right(Rs.Fields(i).Name, 5) = "_priv" Then
    .FieldName = Rs.Fields(i).Name
    .FieldValue = Rs.Fields(i).Value
    Count = Count + 1
   End If
  End With
 Next i
 Database.LoadGroups
 For i = 0 To Database.nGroups - 1
  Database.LoadUsers Database.Groups(i)
  'Load associated global rights for that group
  UserName = GetUserNameFromGroupName(Database.Groups(i))
  Sql = "SELECT * FROM User WHERE User='" & UserName & "'"
  Set Rs = Mct.Execute(Sql)
  Count = 0
  For j = 0 To nFields - 1
   With Fields(Count)
    If Right(Rs.Fields(j).Name, 5) = "_priv" Then
     .FieldValue = Rs.Fields(j).Value
     Count = Count + 1
    End If
   End With
  Next j
  For j = 0 To Database.nUsers - 1
   Sql = "UPDATE user SET "
   For k = 0 To Count - 1
    With Fields(k)
     Sql = Sql & .FieldName & "='" & .FieldValue & "'"
     If k <> Count - 1 Then Sql = Sql & ","
    End With
   Next k
   Sql = Sql & " WHERE user='" & Users(j) & "'"
   DoMySql Mct, Sql
  Next j
 Next i
 '2) columns_priv
 'We must copy all the lines of the group, for each user of the group
 FrmMain.LblProgress.Caption = "Updating columns privs..."
 For i = 0 To Database.nGroups - 1
  Database.LoadUsers Database.Groups(i)
  UserName = GetUserNameFromGroupName(Database.Groups(i))
  For j = 0 To Database.nUsers - 1
   'Delete the lines for the current user
   Sql = "DELETE FROM columns_priv WHERE user='" & Database.Users(j) & "'"
   DoMySql Mct, Sql
   'For each line in table
   Sql = "SELECT Host, DB,User,Table_name,Column_name,Column_priv FROM Columns_priv WHERE"
   Sql = Sql & " user='" & UserName & "'"
   Set Rs = Mct.Execute(Sql)
   If Not (Rs.EOF) Then
    While Not (Rs.EOF)
     'Reading of the current line
     With ColumnPriv
      .Host = Lib.Pure(Rs("Host"))
      .Db = Lib.Pure(Rs("Db"))
      .User = Lib.Pure(Rs("User"))
      .Table_name = Lib.Pure(Rs("Table_Name"))
      .Column_Name = Lib.Pure(Rs("Column_Name"))
      .Column_Priv = Lib.Pure(Rs("Column_Priv"))
      'Create the insert statement
      Sql = "INSERT INTO columns_priv(host, DB, user, table_name, column_name, column_priv) VALUES("
      Sql = Sql & "'" & .Host & "',"
      Sql = Sql & "'" & .Db & "',"
      Sql = Sql & "'" & Database.Users(j) & "',"
      Sql = Sql & "'" & .Table_name & "',"
      Sql = Sql & "'" & .Column_Name & "',"
      Sql = Sql & "'" & .Column_Priv & "')"
      DoMySql Mct, Sql
     End With 'ColumnPriv
     Rs.MoveNext
    Wend
   End If 'Not (Rs.EOF)
  Next j
 Next i
 '3) procs_priv
 'We must copy all the lines of the group, for each user of the group
 FrmMain.LblProgress.Caption = "Updating procs privs..."
 For i = 0 To Database.nGroups - 1
  Database.LoadUsers Database.Groups(i)
  UserName = GetUserNameFromGroupName(Database.Groups(i))
  For j = 0 To Database.nUsers - 1
   'Delete the lines for the current user
   Sql = "DELETE FROM procs_priv WHERE user='" & Database.Users(j) & "'"
   DoMySql Mct, Sql
   'For each line in table
   Sql = "SELECT Host, Db, User, routine_name, routine_type, grantor, proc_priv from procs_priv WHERE"
   Sql = Sql & " user='" & UserName & "'"
   Set Rs = Mct.Execute(Sql)
   If Not (Rs.EOF) Then
    While Not (Rs.EOF)
     'Reading of the current line
     With ProcPriv
      .Host = Lib.Pure(Rs("Host"))
      .Db = Lib.Pure(Rs("Db"))
      .User = Lib.Pure(Rs("User"))
      .Routine_name = Lib.Pure(Rs("routine_name"))
      .Routine_type = Lib.Pure(Rs("routine_type"))
      .Grantor = Lib.Pure(Rs("grantor"))
      .Proc_priv = Lib.Pure(Rs("proc_priv"))
      'Create the insert statement
      Sql = "INSERT INTO procs_priv(host, DB, user, routine_name, routine_type, grantor, proc_priv) VALUES("
      Sql = Sql & "'" & .Host & "',"
      Sql = Sql & "'" & .Db & "',"
      Sql = Sql & "'" & Database.Users(j) & "',"
      Sql = Sql & "'" & .Routine_name & "',"
      Sql = Sql & "'" & .Routine_type & "',"
      Sql = Sql & "'" & .Grantor & "',"
      Sql = Sql & "'" & .Proc_priv & "')"
      DoMySql Mct, Sql
     End With 'ColumnPriv
     Rs.MoveNext
    Wend
   End If 'Not (Rs.EOF)
  Next j
 Next i
 '4) tables_priv
 'We must copy all the lines of the group, for each user of the group
 FrmMain.LblProgress.Caption = "Updating tables privs..."
 For i = 0 To Database.nGroups - 1
  Database.LoadUsers Database.Groups(i)
  UserName = GetUserNameFromGroupName(Database.Groups(i))
  For j = 0 To Database.nUsers - 1
   'Delete the lines for the current user
   Sql = "DELETE FROM tables_priv WHERE user='" & Database.Users(j) & "'"
   DoMySql Mct, Sql
   'For each line in table
   Sql = "SELECT Host, Db, User, table_name, grantor, table_priv, column_priv from tables_priv WHERE"
   Sql = Sql & " user='" & UserName & "'"
   Set Rs = Mct.Execute(Sql)
   If Not (Rs.EOF) Then
    While Not (Rs.EOF)
     'Reading of the current line
     With TablePriv
      .Host = Lib.Pure(Rs("Host"))
      .Db = Lib.Pure(Rs("Db"))
      .User = Lib.Pure(Rs("User"))
      .Table_name = Lib.Pure(Rs("table_name"))
      .Grantor = Lib.Pure(Rs("grantor"))
      .Table_Priv = Lib.Pure(Rs("table_priv"))
      .Column_Priv = Lib.Pure(Rs("column_priv"))
      'Create the insert statement
      Sql = "INSERT INTO tables_priv(host, DB, user, table_name, grantor, table_priv, column_priv) VALUES("
      Sql = Sql & "'" & .Host & "',"
      Sql = Sql & "'" & .Db & "',"
      Sql = Sql & "'" & Database.Users(j) & "',"
      Sql = Sql & "'" & .Table_name & "',"
      Sql = Sql & "'" & .Grantor & "',"
      Sql = Sql & "'" & .Table_Priv & "',"
      Sql = Sql & "'" & .Column_Priv & "')"
      DoMySql Mct, Sql
     End With 'ColumnPriv
     Rs.MoveNext
    Wend
   End If 'Not (Rs.EOF)
  Next j
 Next i
 
 '5) db : database priviledges
 'We must copy all the lines of the group, for each user of the group
 FrmMain.LblProgress.Caption = "Updating db privs..."
 For i = 0 To Database.nGroups - 1
  Database.LoadUsers Database.Groups(i)
  UserName = GetUserNameFromGroupName(Database.Groups(i))
  For j = 0 To Database.nUsers - 1
   'Delete the lines for the current user
   Sql = "DELETE FROM db WHERE user='" & Database.Users(j) & "'"
   DoMySql Mct, Sql
   'For each line in table
   Sql = "SELECT Host, Db, User, Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv, Grant_priv, References_priv, Index_priv, Alter_priv, Create_tmp_table_priv, Lock_tables_priv, Create_view_priv, Show_view_priv, Create_routine_priv,  Alter_routine_priv,  Execute_priv"
   Sql = Sql & " FROM db WHERE user='" & UserName & "'"
   Set Rs = Mct.Execute(Sql)
   If Not (Rs.EOF) Then
    While Not (Rs.EOF)
     'Reading of the current line
     With Db
      .Host = Lib.Pure(Rs("Host"))
      .Db = Lib.Pure(Rs("Db"))
      .User = Lib.Pure(Rs("User"))
      .Select_priv = Lib.Pure(Rs("Select_priv"))
      .Insert_priv = Lib.Pure(Rs("Insert_priv"))
      .Update_priv = Lib.Pure(Rs("Update_priv"))
      .Delete_priv = Lib.Pure(Rs("Delete_priv"))
      .Create_priv = Lib.Pure(Rs("Create_priv"))
      .Drop_priv = Lib.Pure(Rs("Drop_priv"))
      .Grant_priv = Lib.Pure(Rs("Grant_priv"))
      .References_priv = Lib.Pure(Rs("References_priv"))
      .Index_priv = Lib.Pure(Rs("Index_priv"))
      .Alter_priv = Lib.Pure(Rs("Alter_priv"))
      .Create_tmp_table_priv = Lib.Pure(Rs("Create_tmp_table_priv"))
      .Lock_tables_priv = Lib.Pure(Rs("Lock_tables_priv"))
      .Create_view_priv = Lib.Pure(Rs("Create_view_priv"))
      .Show_view_priv = Lib.Pure(Rs("Show_view_priv"))
      .Create_routine_priv = Lib.Pure(Rs("Create_routine_priv"))
      .Alter_routine_priv = Lib.Pure(Rs("Alter_routine_priv"))
      .Execute_priv = Lib.Pure(Rs("Execute_priv"))
      'Create the insert statement
      Sql = "INSERT INTO db(Host, Db, User, Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv, Grant_priv, References_priv, Index_priv, Alter_priv, Create_tmp_table_priv, Lock_tables_priv, Create_view_priv, Show_view_priv, Create_routine_priv,  Alter_routine_priv,  Execute_priv) VALUES("
      Sql = Sql & "'" & .Host & "',"
      Sql = Sql & "'" & .Db & "',"
      Sql = Sql & "'" & Database.Users(j) & "',"
      Sql = Sql & "'" & .Select_priv & "',"
      Sql = Sql & "'" & .Insert_priv & "',"
      Sql = Sql & "'" & .Update_priv & "',"
      Sql = Sql & "'" & .Delete_priv & "',"
      Sql = Sql & "'" & .Create_priv & "',"
      Sql = Sql & "'" & .Drop_priv & "',"
      Sql = Sql & "'" & .Grant_priv & "',"
      Sql = Sql & "'" & .References_priv & "',"
      Sql = Sql & "'" & .Index_priv & "',"
      Sql = Sql & "'" & .Alter_priv & "',"
      Sql = Sql & "'" & .Create_tmp_table_priv & "',"
      Sql = Sql & "'" & .Lock_tables_priv & "',"
      Sql = Sql & "'" & .Create_view_priv & "',"
      Sql = Sql & "'" & .Show_view_priv & "',"
      Sql = Sql & "'" & .Create_routine_priv & "',"
      Sql = Sql & "'" & .Alter_routine_priv & "',"
      Sql = Sql & "'" & .Execute_priv & "')"
      DoMySql Mct, Sql
     End With 'Db
     Rs.MoveNext
    Wend
   End If 'Not (Rs.EOF)
  Next j
 Next i
 FrmMain.CmdApply.Enabled = True
 Lib.AdLog "Ending Synchronization"
 FrmMain.LblProgress.Caption = ""
End Sub

Private Function GetUserNameFromGroupName(Group As String) As String
 Dim Rs2 As New ADODB.Recordset
 Dim Sql As String
 Sql = "SELECT user FROM user_info WHERE Description='GROUP_" & Group & "'"
 Set Rs2 = Mct.Execute(Sql)
 GetUserNameFromGroupName = Lib.Pure(Rs2("user"))
 Set Rs2 = Nothing
End Function

Public Sub NonUserToUser(Group As String, UserName As String)
 Dim Already As Boolean
 'See if a record exist in user_info
 Sql = "SELECT user from user_info WHERE user='" & UserName & "'"
 Set Rs = Mct.Execute(Sql)
 Already = Not (Rs.EOF)
 If Not (Already) Then
  'INSERT
  Sql = "INSERT INTO user_info(user,description) VALUES("
  Sql = Sql & "'" & UserName & "',"
  Sql = Sql & "'MEMBER_" & Group & "')"
  DoMySql Mct, Sql
  Else
   'UPDATE
   Sql = "UPDATE user_info SET description='MEMBER_" & Group & "' WHERE user='" & UserName & "'"
   DoMySql Mct, Sql
 End If 'Not(Already)
End Sub

Public Sub UserToNonUser(UserName As String)
 Sql = "UPDATE user_info SET Description='' WHERE user='" & UserName & "'"
 DoMySql Mct, Sql
End Sub

Public Function OpenDb() As Byte
 On Error Resume Next
 Mct.ConnectionString = Lib.AppSettings.ConnString
 Mct.Open
 If Err Then
  OpenDb = 1
  Lib.SayError Err.Description & " (" & Err.Number & ")"
 End If
End Function

Public Function CloseDb() As Byte
 On Error Resume Next
 Mct.Close
 If Err Then
  CloseDb = 1
 End If
End Function

Public Sub LoadGroups()
 Dim Count As Integer
 Sql = "SELECT Count(Description) as Total FROM user_info WHERE Description Like 'GROUP_%'"
 Set Rs = Mct.Execute(Sql)
 nGroups = Rs("Total")
 If nGroups > 0 Then
  ReDim Groups(nGroups - 1)
  Sql = "SELECT Description FROM user_info WHERE Description Like 'GROUP_%'"
  Set Rs = Mct.Execute(Sql)
  While Not (Rs.EOF)
   Groups(Count) = Lib.GroupName(Lib.Pure(Rs("Description")))
   Count = Count + 1
   Rs.MoveNext
  Wend
 End If 'nGroups > 0
End Sub

Public Sub LoadUsers(Group As String)
 Dim Count As Integer
 Dim Count2 As Integer
 Dim Msk As String
 Dim User As String
 Dim Description As String
 Msk = "MEMBER_" & Group
 'Getting the number of members belonging to the group
 Sql = "SELECT Count(User) as Total FROM user_info WHERE Description='" & Msk & "'"
 Set Rs = Mct.Execute(Sql)
 nUsers = Rs("Total")
 If nUsers > 0 Then ReDim Users(nUsers - 1)
 'Getting the number of members which are NOT in the group
 Sql = "SELECT COUNT(user.user) As Total FROM User left join user_info"
 Sql = Sql & " ON user.user=user_info.user"
 Sql = Sql & " WHERE user.user<>'root' AND (user_info.description Not like 'GROUP_%'"
 Sql = Sql & " AND Description<>'" & Msk & "' OR user_info.description is null)"
 Set Rs = Mct.Execute(Sql)
 nNonUsers = Rs("Total")
 If nNonUsers > 0 Then ReDim NonUsers(nNonUsers - 1)
 Sql = "SELECT user.user, user_info.description FROM User left join user_info"
 Sql = Sql & " ON user.user=user_info.user"
 Sql = Sql & " WHERE user.user<>'root'"
 Set Rs = Mct.Execute(Sql)
 While Not (Rs.EOF)
  Description = ""
  User = Lib.Pure(Rs("User"))
  On Error Resume Next
  Description = Lib.Pure(Rs("Description"))
  On Error GoTo 0
  If Left(Description, 6) <> "GROUP_" Then
   If Description = Msk Then
    'Is Member
    Users(Count) = User
    Count = Count + 1
    Else
    'Is Not Member
    NonUsers(Count2) = User
    Count2 = Count2 + 1
   End If 'Description = Msk
  End If 'Left(Description, 6)
  Rs.MoveNext
 Wend
End Sub

Private Sub DoMySql(ByRef Mct As ADODB.Connection, Sql As String)
 On Error Resume Next
 'Lib.AdLog Sql
 Mct.Execute Sql
 If Err Then
  Lib.SayError Err.Description & " (" & Err.Number & ")"
  Err.Clear
 End If
End Sub
